﻿Imports Microsoft.Research.CommunityTechnologies
Imports System.Data.SqlClient
Imports SQLSpaceMapper.Enums


Public Class MainForm

    Dim DBTablesDictionary As New Dictionary(Of String, Table)
    'Dim ConnectionString As String


    Private Sub BuildTreemap()

        tmcMap.Clear()

        Dim MapCollection As New List(Of Treemap.Node)

        For Each table In DBTablesDictionary.Values

            Dim CurrentTableData As Integer = table.DataSize

            Dim TableNode As New Treemap.Node(table.Name, table.TotalSize, 75)
            If table.Type = ObjectTypes.View Then
                TableNode.Text = String.Concat(TableNode.Text, " (View)")
            End If

            TableNode.ToolTip = String.Concat("Total size, including indexes: ", KBToString(table.TotalSize))

            Dim datanode As Treemap.Node

            If table.Storage = PhysicalTable.Clustered Then
                datanode = TableNode.Nodes.Add("Clustered Index", table.DataSize, 50)
            ElseIf table.Storage = PhysicalTable.Heap Then
                datanode = TableNode.Nodes.Add("Heap Data", table.DataSize, 50)
            End If
            DataNode.ToolTip = String.Concat("Row Data: ", KBToString(CurrentTableData))

            Dim indexnode As New Treemap.Node("Indexes", table.IndexSize, 50)

            Dim TotalIndexSize As Integer

            For Each i As Index In table.Indexes.Values

                Dim Index As New Treemap.Node(i.Name, i.Size, 25)
                Index.ToolTip = String.Concat(i.Name, ControlChars.CrLf, KBToString(i.Size))
                indexnode.Nodes.Add(Index)
                TotalIndexSize += i.Size
            Next

            If table.Indexes.Count > 0 Then
                indexnode.ToolTip = String.Concat("Total size of indexes: ", KBToString(TotalIndexSize))
                TableNode.Nodes.Add(indexnode)
            End If

            mapcollection.Add(TableNode)

        Next

        RedrawTreeMap(MapCollection)

    End Sub

    Public Delegate Sub RedrawTreeMapDelegate(ByVal MapCollection As List(Of Treemap.Node))
    Public Sub RedrawTreeMap(ByVal MapCollection As List(Of Treemap.Node))
        If Me.InvokeRequired Then
            Me.Invoke(New RedrawTreeMapDelegate(AddressOf RedrawTreeMap), MapCollection)
        Else
            For Each node As Treemap.Node In MapCollection
                Me.tmcMap.Nodes.Add(node)
            Next
        End If
    End Sub

    Private Sub RefreshDatabaseView()

        Dim sc As New SqlConnection(SQLConnectionProperties.ConnectionString)
        sc.Open()

        Dim CanSeeAllTables As Boolean
        Dim sql As New SqlCommand("SELECT IS_ROLEMEMBER('db_ddladmin') | IS_ROLEMEMBER('db_datareader') | IS_ROLEMEMBER('db_owner') | IS_ROLEMEMBER('db_datawriter') | IS_ROLEMEMBER('db_accessadmin') | IS_ROLEMEMBER('db_securityadmin')", sc)
        CanSeeAllTables = sql.ExecuteScalar

        ' This is the 2000-compatible command
        'sql = New SqlCommand("select object_name(t.id) as TableName, t.id, t.dpages*8 as Pages, rowcnt as TableRows  from sysindexes t  join sysobjects so    on t.id = so.id        where(t.id > 100)    and indid in (0,1)    and so.type = 'U' order by object_name(t.id);", sc)
        ' This command uses sys.schemas (2005+), but supports the schema name directly.
        sql = New SqlCommand("select t.id as object_id, '[' + ss.name + '].[' + object_name(t.id) + ']' as TableName, t.id, t.dpages*8 as Pages, rowcnt as TableRows, so.type, t.indid as [Clustered]  from sysindexes t  join sysobjects so    on t.id = so.id  join sys.schemas ss on so.uid = ss.schema_id      where(t.id > 100)    and indid in (0,1)    and so.type IN ('U', 'V') order by object_name(t.id);", sc)
        sql.CommandTimeout = 0
        sql.CommandType = CommandType.Text

        Dim ds As New DataSet
        Dim da As New SqlDataAdapter(sql)

        da.Fill(ds)

        Me.DBTablesDictionary.Clear()

        ' Create dictionary to hold indexes
        Dim indexcollection As New Dictionary(Of Long, List(Of DataRow))

        ' Lookup indexes and add them to dictionary
        Using ds2 As New DataSet
            sql = New SqlCommand("select id as object_id, name, dpages*8 as Pages from sysindexes where dpages > 0  and indid not in (0,1) order by id", sc)

            Dim da2 As New SqlDataAdapter(sql)

            da2.Fill(ds2)

            ' Walk through indexes and create a quick dictionary
            For Each dr As DataRow In ds2.Tables(0).Rows
                If Not indexcollection.ContainsKey(dr.Item("object_id")) Then
                    indexcollection.Add(dr.Item("object_id"), New List(Of DataRow))
                End If
                indexcollection.Item(dr.Item("object_id")).Add(dr)
            Next

        End Using

        ' Create dictionary to hold full-text indexes
        Dim fulltextdictionary As New Dictionary(Of Long, List(Of DataRow))

        ' Lookup FTIs and add them to dictionary
        Using ds2 As New DataSet
            sql = New SqlCommand("SELECT fi.object_id, name as CatalogName, sum(fif.data_size)/1024 as SizePages, sum(fif.row_count) as [Rows] FROM sys.fulltext_catalogs fc join sys.fulltext_indexes fi on fc.fulltext_catalog_id = fi.fulltext_catalog_id join sys.fulltext_index_fragments fif on fi.object_id = fif.table_id GROUP BY fi.object_id, Name", sc)

            Dim da2 As New SqlDataAdapter(sql)

            da2.Fill(ds2)

            ' Walk through indexes and create a quick dictionary
            For Each dr As DataRow In ds2.Tables(0).Rows
                If Not fulltextdictionary.ContainsKey(dr.Item("object_id")) Then
                    fulltextdictionary.Add(dr.Item("object_id"), New List(Of DataRow))
                End If
                fulltextdictionary.Item(dr.Item("object_id")).Add(dr)
            Next

        End Using



        For Each dr As DataRow In ds.Tables(0).Rows

            Dim ObjectType As ObjectTypes
            Select Case dr.Item("Type").ToString.Trim
                Case "U"
                    ' User table
                    ObjectType = ObjectTypes.Table
                Case "V"
                    ' Clustered view
                    ObjectType = ObjectTypes.View
            End Select

            Me.DBTablesDictionary.Add(dr.Item("TableName"), New Table(dr.Item("TableName"), dr.Item("Pages"), ObjectType, dr.Item("Clustered")))

            ' Index details
            If indexcollection.ContainsKey(dr.Item("object_id")) Then
                For Each dr2 As DataRow In indexcollection.Item(dr.Item("object_id"))
                    Me.DBTablesDictionary.Item(dr.Item("TableName")).Indexes.Add(dr2.Item("name"), New Index(dr2.Item("name"), dr2.Item("pages")))
                Next
            End If

            ' Full text index details (if present)
            If fulltextdictionary.ContainsKey(dr.Item("object_id")) Then
                ' There is a full text catalog on this table - add it
                ' Only does one per table - currently (as of 2012), that's all that's allowed
                Dim ftc As DataRow = fulltextdictionary.Item(dr.Item("object_id")).Item(0)
                Me.DBTablesDictionary.Item(dr.Item("TableName")).Indexes.Add( _
                            String.Concat(ftc.Item("CatalogName"), " (Fulltext)"), _
                            New Index(String.Concat(ftc.Item("CatalogName"), " (Fulltext)"), ftc.Item("SizePages")))
            Else
                ' No full text on this table
            End If
        Next

        BuildTreemap()

        If Not CanSeeAllTables Then
            MessageBox.Show("This account does not belong to any database admin roles. Some objects may not be visible to this user", _
                            "Low user permissions", MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        Me.lblCurrentActivity.Text = String.Concat(SQLConnectionProperties.Database, " on ", _
                                           SQLConnectionProperties.Server, " as ", _
                                           If(SQLConnectionProperties.IntegratedSecurity, My.User.Name, SQLConnectionProperties.Username))

    End Sub



    Private Sub mnuConnect_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuConnect.Click

        Using f As New DBLogin
            f.ShowDialog()
            If SQLConnectionProperties.ConnectionString <> String.Empty Then
                Me.mnuDatabases.DropDownItems.Clear()

                For Each db As String In f.cboDatabases.Items
                    Me.mnuDatabases.DropDownItems.Add(db, Me.mnuDatabases.Image, AddressOf ChangeDatabase)
                Next

                Me.mnuDatabases.Enabled = Me.mnuDatabases.DropDownItems.Count
                Me.mnuRefresh.Enabled = Me.mnuDatabases.DropDownItems.Count

                RefreshDatabaseView()
            End If
        End Using

    End Sub

    Private Sub ChangeDatabase(ByVal sender As System.Object, ByVal e As System.EventArgs)
        SQLConnectionProperties.Database = sender.ToString
        RefreshDatabaseView()
    End Sub

    Private Sub mnuExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExit.Click
        End
    End Sub

    Private Sub mnuAbout_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuAbout.Click
        About.ShowDialog()
    End Sub

    Private Sub mnuRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuRefresh.Click
        RefreshDatabaseView()
    End Sub

    Private Function KBToString(ByVal KB As Long) As String
        Dim Bytes As Decimal = KB
        Dim M As String = "KB"

        If Bytes > 1024 Then
            Bytes = Bytes / 1024
            M = "MB"
        End If

        If Bytes > 1024 Then
            Bytes = Bytes / 1024
            M = "GB"
        End If

        If Bytes > 1024 Then
            Bytes = Bytes / 1024
            M = "TB"
        End If

        Return String.Concat(Decimal.Round(Bytes, 1), " ", M)

    End Function

    Private Sub mnuView_Partitions_Click(sender As System.Object, e As System.EventArgs) Handles mnuView_Partitions.Click
        ' Reverse the checked state
        Me.mnuView_Partitions.Checked = Not Me.mnuView_Partitions.Checked

        ' Then refresh the display
        BuildTreemap()
    End Sub

    Private Sub MainForm_Shown(sender As Object, e As System.EventArgs) Handles Me.Shown
        ' Load the window to allow database login on launch
        mnuConnect_Click(sender, e)

    End Sub

    Private Sub mnuView_SystemObjects_Click(sender As System.Object, e As System.EventArgs) Handles mnuView_SystemObjects.Click
        ' Reverse the checked state
        Me.mnuView_SystemObjects.Checked = Not Me.mnuView_SystemObjects.Checked

        ' Then refresh the display
        BuildTreemap()
    End Sub
End Class
